unit unitadmalumnos;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, DBGrids,
  Menus, unitmainform, sqldb, db, unitalumnos;

type

  { TFormAdmAlumnos }

  TFormAdmAlumnos = class(TForm)
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    MainMenu1: TMainMenu;
    MenuItem1: TMenuItem;
    MenuItem2: TMenuItem;
    MenuItem3: TMenuItem;
    MenuItem4: TMenuItem;
    MenuItem5: TMenuItem;
    SQLQuery1: TSQLQuery;
    SQLQuery2: TSQLQuery;
    procedure DBGrid1CellClick(Column: TColumn);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure MenuItem2Click(Sender: TObject);
    procedure MenuItem4Click(Sender: TObject);
    procedure MenuItem5Click(Sender: TObject);
    procedure ordenar_grid();
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    alm_apellido : string;
  end;

var
  FormAdmAlumnos: TFormAdmAlumnos;

implementation

{$R *.lfm}

{ TFormAdmAlumnos }

procedure TFormAdmAlumnos.ordenar_grid();
begin
  if SQLQuery1.Active = true then
    SQLQuery1.Close;

  SQLQuery1.SQL.Text := 'select * from alumno where alm_apellido ilike ''%' + alm_apellido + '%'' order by alm_apellido';

  SQLQuery1.Open;

  TDateTimeField(SQLQuery1.FieldByName('alm_fecha_nac')).DisplayFormat := ('DD/MM/YYYY');
  TDateTimeField(SQLQuery1.FieldByName('alm_fecha_alta')).DisplayFormat := ('DD/MM/YYYY');

  DBGrid1.Columns[4].Visible := false;
  DBGrid1.Columns[5].Visible := false;

  DBGrid1.Columns[0].Title.Caption := 'DNI';
  DBGrid1.Columns[0].Width := 80;
  DBGrid1.Columns[1].Title.Caption := 'Apellido';
  DBGrid1.Columns[1].Width := 100;
  DBGrid1.Columns[2].Title.Caption := 'Nombre';
  DBGrid1.Columns[2].Width := 150;
  DBGrid1.Columns[3].Title.Caption := 'Fecha Nac.';
  DBGrid1.Columns[3].Width := 90;
  DBGrid1.Columns[4].Title.Caption := 'Direccion';
  DBGrid1.Columns[4].Width := 150;
  DBGrid1.Columns[5].Title.Caption := 'Barrio';
  DBGrid1.Columns[5].Width := 100;
  DBGrid1.Columns[6].Title.Caption := 'Telefono';
  DBGrid1.Columns[6].Width := 80;
  DBGrid1.Columns[7].Title.Caption := 'Celular';
  DBGrid1.Columns[7].Width := 80;
  DBGrid1.Columns[8].Title.Caption := 'Certificado';
  DBGrid1.Columns[8].Width := 80;
  DBGrid1.Columns[9].Title.Caption := 'Fotoc. DNI';
  DBGrid1.Columns[9].Width := 80;
  DBGrid1.Columns[10].Title.Caption := 'Planilla';
  DBGrid1.Columns[10].Width := 80;
  DBGrid1.Columns[11].Title.Caption := 'Fecha Alta';
  DBGrid1.Columns[11].Width := 90;
  DBGrid1.Columns[12].Title.Caption := 'Alumno Activo?';
  DBGrid1.Columns[12].Width := 110;
end;

procedure TFormAdmAlumnos.FormShow(Sender: TObject);
begin
  alm_apellido := '%';
  ordenar_grid();
end;

procedure TFormAdmAlumnos.FormClose(Sender: TObject;
  var CloseAction: TCloseAction);
begin
  SQLQuery1.Close;
  SQLQuery2.Close;
end;

procedure TFormAdmAlumnos.DBGrid1CellClick(Column: TColumn);
begin
  FormAlumnos.Caption := 'Modificacion de Alumno';
  FormAlumnos.Edit1.Text := SQLQuery1.FieldByName('alm_dni').AsString;
  FormAlumnos.Edit1.Enabled := false;
  FormAlumnos.Edit2.Text := SQLQuery1.FieldByName('alm_apellido').AsString;
  FormAlumnos.Edit3.Text := SQLQuery1.FieldByName('alm_nombre').AsString;
  FormAlumnos.Edit4.Text := FormatDateTime('dd/mm/yyyy', SQLQuery1.FieldByName('alm_fecha_nac').AsDateTime);
  FormAlumnos.Edit5.Text := SQLQuery1.FieldByName('alm_direccion').AsString;
  FormAlumnos.Edit6.Text := SQLQuery1.FieldByName('alm_barrio').AsString;
  FormAlumnos.Edit7.Text := SQLQuery1.FieldByName('alm_telefono').AsString;
  FormAlumnos.Edit8.Text := SQLQuery1.FieldByName('alm_celular').AsString;
  FormAlumnos.CheckBox1.Checked := SQLQuery1.FieldByName('alm_presento_certificado').AsBoolean;
  FormAlumnos.CheckBox2.Checked := SQLQuery1.FieldByName('alm_presento_fotoc_dni').AsBoolean;
  FormAlumnos.CheckBox3.Checked := SQLQuery1.FieldByName('alm_presento_planilla').AsBoolean;
  FormAlumnos.CheckBox4.Checked := SQLQuery1.FieldByName('alm_activo').AsBoolean;
  FormAlumnos.CheckBox4.Visible := true;
  FormAlumnos.Button1.Caption := 'Modificar Datos Alumno';
  FormAlumnos.Button1.Enabled := true;
  FormAlumnos.ShowModal;

  ordenar_grid();
end;

procedure TFormAdmAlumnos.MenuItem2Click(Sender: TObject);
begin
  Close();
end;

procedure TFormAdmAlumnos.MenuItem4Click(Sender: TObject);
begin
  FormAlumnos.Caption := 'Alta de Alumno';
  FormAlumnos.Edit1.Text := '';
  FormAlumnos.Edit2.Text := '';
  FormAlumnos.Edit3.Text := '';
  FormAlumnos.Edit4.Text := '';
  FormAlumnos.Edit5.Text := '';
  FormAlumnos.Edit6.Text := '';
  FormAlumnos.Edit7.Text := '';
  FormAlumnos.Edit8.Text := '';
  FormAlumnos.CheckBox1.Checked := false;
  FormAlumnos.CheckBox2.Checked := false;
  FormAlumnos.CheckBox3.Checked := false;
  FormAlumnos.CheckBox4.Checked := true;
  FormAlumnos.CheckBox4.Visible := false;
  FormAlumnos.Button1.Caption:='Guardar Nuevo Alumno';
  FormAlumnos.ShowModal;

  ordenar_grid();
end;

procedure TFormAdmAlumnos.MenuItem5Click(Sender: TObject);
var
  fecha : string;
  confirmacion : integer;
  QueryString : string;

begin
  fecha := InputBox('Desactivando alumnos por inasistencia', 'Ingrese la fecha de base:', FormatDateTime('dd/mm/yyyy', date));

  if fecha <> '' then
  begin
    confirmacion := MessageDlg('Desactivar todos los alumnos que no hayan asistido a clase despues del ' + fecha + ' ?'
      ,mtConfirmation, mbYesNo, 0);

    if confirmacion = mrYes then
    begin
      fecha := fecha + ' 00:00:00';

      if SQLQuery2.Active = true then
        SQLQuery2.Close;

      SQLQuery2.SQL.Text := 'select desactivar_alumnos(''' + fecha + ''')';

      SQLQuery2.Open;

      ordenar_grid();

      while not SQLQuery2.EOF do
      begin
        ShowMessage(SQLQuery2.FieldByName('desactivar_alumnos').AsString + ' alumnos desactivados');
        SQLQuery2.Next;
      end;

      SQLQuery2.Close;
    end;
  end;

end;

procedure TFormAdmAlumnos.FormCreate(Sender: TObject);
begin
  SQLQuery1.DataBase := MainForm.PQConnection1;
  SQLQuery1.Transaction := MainForm.SQLTransaction1;
  SQLQuery2.DataBase := MainForm.PQConnection1;
  SQLQuery2.Transaction := MainForm.SQLTransaction1;
end;

end.

